//Group 3
// Charles Eberhardt, Colton Hurt, David Barbee, John Johnson
import java.sql.*;
import java.util.Scanner;

public class Problem2Main{
	
	private static final String connectionString = "jdbc:oracle:thin:@stu-oracle.cs.ou.edu:1521:cs4513db";
	private static final String loginName = "john5814";
	private static final String password = "CBzr9Gl7";
	
	public static void main(String[] args) throws Exception{
		try{
			Class.forName("oracle.jdbc.OracleDriver");
		}
		catch (Exception e){
			System.out.println("Unable to load the driver class.");
		}
		final Connection dbConnection = DriverManager.getConnection(connectionString,loginName,password); // Creates the Connection to the database
		final Statement stmt = dbConnection.createStatement(); 
		
		Scanner s = new Scanner(System.in);
		boolean quit = false; // Used to quit the program
		int userInput; // Used to get the menu selection from the user. 
		while(!quit) 
		{
			System.out.print("1 - Add Faculty\n2 - Add Faculty no dept\n3 - Display Faculty Information\n4 - Quit\n");
			userInput = s.nextInt();
			
			switch(userInput){ 
			case 1: option1(dbConnection); break;
			case 2: option2(dbConnection); break;
			case 3: option3(stmt); break;
			case 4: quit = true; break; // If the user selects 4, this will break the loop and consequently close the program.
			}
			
			System.out.println(""); // Adds a new line before the menu is printed again.
		}
		s.close(); // Closes the Scanner
		stmt.close(); // Closes the Statement
		dbConnection.close(); //Closes the Connection to the databse
	}
	private static void option1 (Connection dbConnection) throws Exception
	{
		Scanner s = new Scanner(System.in);
		System.out.println("Faculty ID?");
		int id = s.nextInt();
		
		System.out.println("Faculty Name?");
		String name = s.next();
		
		System.out.println("Faculty Department?");
		int dept = s.nextInt();
		
		CallableStatement cs = dbConnection.prepareCall("{call insertFaculty("+id+",'"+name+"',"+dept+")}");
		cs.execute();
		
	}
	private static void option2 (Connection dbConnection) throws Exception
	{
		Scanner s = new Scanner(System.in);
		System.out.println("Faculty ID?");
		int id = s.nextInt();
		
		System.out.println("Faculty Name?");
		String name = s.next();
		
		System.out.println("Faculty Department?");
		int dept = s.nextInt();
		
		System.out.println("Department to exclude from average?");
		int some_dept = s.nextInt();
		// The preceding lines get the new faculty memeber's information from the user. 
		
		CallableStatement cs = dbConnection.prepareCall("{call insertFaculty2("+id+",'"+name+"',"+dept+","+some_dept+")}"); // Creates a statement to call the insertFacutly2 stored procedure
		cs.execute(); // Executes the statement
	}
	private static void option3 (Statement stmt)
	{
		String selectStmt = "select * from Faculty";
		String result = "fid\tfname\tdeptid\tsalary\n"; // This string will contain all the information from the faculty. This will be printed to the console.
		try {
			ResultSet rs = stmt.executeQuery(selectStmt); // Gets the results of the query and stores them in rs.
			while(rs.next()){ // Loops through rs 
				int fid = rs.getInt("fid"); // Gets the current fid value
				String fname = rs.getString("fname"); // Gets the current fname value
				int deptid = rs.getInt("deptid"); // Gets the current deptid value
				double salary = rs.getDouble("salary"); // Gets the current salary value
				result += fid+"\t"+fname+"\t"+deptid+"\t"+salary+"\n"; // Appends the current information into the result string.
			}
			System.out.print(result); // Prints the result string to the console to display the information of all the faculty members. 
									  // This is inside the try statment so that it will not display if there is an error with the query. 
		}
		catch(SQLException e){
			System.err.println("SQLException: "+e.getMessage());
		}
	}
}
